using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Text;
using System.Linq;

namespace Twipler.Data.Cache
{
    public class TableStore<T> : DbConnection where T : IReadWriteTableStore
	{
		public string TableName { get; private set; }

		public static TableStore<T> CreateNew(params object[] args)
		{
			TableStore<T> instance = Activator.CreateInstance<TableStore<T>>();
			instance.Drop();

			return Open(args);
		}

    	public static TableStore<T> Open(params object[] args)
		{
			TableStore<T> instance = Activator.CreateInstance<TableStore<T>>();

			T rowStore = Activator.CreateInstance<T>();

			if (args.Length != rowStore.TableNameParamCount)
				throw new ArgumentException("Please specify " + rowStore.TableNameParamCount + " parameters");

			instance.TableName = string.Format(rowStore.TableName, args);

			instance.Initialise();

			return instance;
		}

		private void Drop()
		{
			ExecuteSql(string.Format("if exists (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '{0}') begin truncate table {0} drop table {0} end; ", TableName));
		}

        internal void Truncate()
        {
            ExecuteSql(string.Format("if exists (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '{0}') truncate table {0}; ", TableName));
        }

		private IReadWriteTableStore ReadRow(SqlDataReader reader)
        {
            IReadWriteTableStore instance = Activator.CreateInstance<T>();
            instance.FromSqlDataReader(reader);
            return instance;         
        }

        internal IEnumerable<TableColumn> Columns
        {
            get
            {
            	T instance = Activator.CreateInstance<T>();
                return instance.Columns;
            }
        }

		private string InitialiseSql
		{
			get
			{
				return string.Format("if not exists " +
				                     "(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '{0}') " +
									 "CREATE TABLE {0} ({1}); ", TableName, ColumnsForCreateTable);
			}
		}

		private string UpdateSql
		{
			get
			{
				return string.Format("if exists (SELECT * FROM {0} WHERE {4}) " +
									 "UPDATE {0} SET {1} WHERE {4} " +
									 "ELSE " +
                                     "INSERT INTO {0} ({2}) VALUES ({3});", TableName, ColumnsForSetFromParams, ColumnNames, ColumnsParams, ColumnPrimaryKey.EqualsParam );
			}
		}

        private string SelectRowSql
        {
            get
            {
                return string.Format("SELECT {0} FROM {1} WHERE {2}", ColumnNames, TableName, ColumnPrimaryKey.EqualsParam);
            }
        }

        private string SelectAllSql
        {
            get
            {
                return string.Format("SELECT {0} FROM {1}", ColumnNames, TableName);
            }
        }
        
        internal virtual void Initialise()
        {
            ExecuteSql(InitialiseSql);
        }
        
        public void Add(params object[] args)
        {
			ExecuteInsert(args);
        }
        
        public T Find(params object[] args) 
        {
        	return ExecuteRow(args);
        }

        public List<T> ReadAll()
        {
            return ExecuteList(SelectAllSql);
        }

		public List<T> ReadAllExcluding<U>(TableStore<U> exclude) where U : IReadWriteTableStore
        {
            return ExecuteList( 
                string.Format("SELECT * FROM {0} A LEFT JOIN {1} B ON A.{2} = B.{3} WHERE B.{2} IS NULL ORDER BY 1 DESC",
                TableName, exclude.TableName, ColumnPrimaryKey.Name, exclude.ColumnPrimaryKey.Name));                
        }

        public List<T> ReadAllMutual<U>(TableStore<U> exclude) where U : IReadWriteTableStore
        {
            return ExecuteList(
				string.Format("SELECT * FROM {0} A JOIN {1} B ON A.{2} = B.{3} ORDER BY 1 DESC",
                TableName, exclude.TableName, ColumnPrimaryKey.Name, exclude.ColumnPrimaryKey.Name));
        }

		public void Copy()
		{
			CopyDrop();

			// If "newName" exists, truncate/drop table
			// If "current" exists, rename it to "newName"
			ExecuteSql(string.Format("if exists (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '{0}') exec sp_rename '{0}', '{0}_copy'", TableName));
		}

		public void CopyDrop()
		{
			ExecuteSql( string.Format( "if exists (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '{0}_copy') begin truncate table {0}_copy drop table {0}_copy end; ", TableName));
		}

		public List<T> CopiesDifference()
		{
			T inst = Activator.CreateInstance<T>();
			string primaryKey = inst.Columns.Single(col => col.PrimaryKey).Name;

			return ExecuteList(string.Format("SELECT t1.{1} FROM {0}_copy t1 LEFT JOIN {0} t2 on t1.{1}=t2.{1} WHERE t2.{1} is null", TableName, primaryKey));
		}
        
    	#region -- Boring private helper methods --

		private string _columnsForCreateTable;
		private string ColumnsForCreateTable
		{
			get
			{
				if (_columnsForCreateTable == null)
					_columnsForCreateTable = BuildColumnList(Columns, (sb, col) => sb.AppendFormat("{0} {1}", col.Name, col.SqlType));

				return _columnsForCreateTable;
			}
		}

        private string _columnsForSelect;
        private string ColumnsForSelect
        {
            get
            {
                if (_columnsForSelect == null)
                    _columnsForSelect = BuildColumnList(Columns, (sb, col) => sb.AppendFormat("{0} = {1}", col.Name, col.ParamName));

                return _columnsForSelect;
            }
        }

        private string _columnNames;
        private string ColumnNames
        {
            get
            {
                if (_columnNames == null)
                    _columnNames = BuildColumnList(Columns, (sb, col) => sb.AppendFormat("{0}", col.Name));

                return _columnNames;
            }
        }

		private string _columnsParams;
		private string ColumnsParams
		{
			get
			{
				if (_columnsParams == null)
					_columnsParams = BuildColumnList(Columns, (sb, col) => sb.AppendFormat("{0}", col.ParamName));

				return _columnsParams;
			}
		}

        private string _columnsForSetFromParams;
        private string ColumnsForSetFromParams
        {
            get
            {
                if (_columnsForSetFromParams == null)
                    _columnsForSetFromParams = BuildColumnList(Columns, (sb, column) => sb.AppendFormat("{0} = {1}", column.Name, column.ParamName));

                return _columnsForSetFromParams;
            }
        }

        private TableColumn _columnPrimaryKey;
        private TableColumn ColumnPrimaryKey
        {
            get
            {
                if (_columnPrimaryKey == null)
                    _columnPrimaryKey = Columns.First(c => c.PrimaryKey);

                return _columnPrimaryKey;
            }
        }
		
		private static string BuildColumnList(IEnumerable<TableColumn> columns, Action<StringBuilder, TableColumn> builder)
		{
			StringBuilder list = new StringBuilder();
			foreach (TableColumn column in columns)
			{
				builder(list, column);
				list.Append(", ");
			}

			list.Remove(list.Length - 2, 2);
			return list.ToString();
		}
		#endregion
        
		private void ExecuteInsert(params object[] args)
		{
			T instance = Activator.CreateInstance<T>();

			using (SqlCommand command = new SqlCommand(UpdateSql, Connection))
			{
				Connection.Open();
				try
				{
					instance.QueryParams(command, args);
					command.ExecuteNonQuery();
				}
				finally
				{
					Connection.Close();
				}
			}
		}

		private T ExecuteRow(params object[] args) 
        {
            T instance = Activator.CreateInstance<T>();

            using (SqlCommand command = new SqlCommand(SelectRowSql, Connection))
            {
                Connection.Open();
                try
                {
                    instance.QueryParams(command, args);

                    SqlDataReader reader = command.ExecuteReader();
                    if (reader.Read())
                        instance.FromSqlDataReader(reader);
                }
                finally
                {
                    Connection.Close();
                }
            }

            return instance;
        }

		private List<T> ExecuteList(string sql) 
        {
            List<T> list = new List<T>();

            using (SqlCommand command = new SqlCommand(sql, Connection))
            {
                Connection.Open();
                try
                {
                    SqlDataReader reader = command.ExecuteReader();
                    while (reader.Read())
                        list.Add((T)ReadRow(reader));
                }
                finally
                {
                    Connection.Close();
                }
            }

            return list;
        }

		private void ExecuteSql(string Sql)
        {
            using (SqlCommand command = new SqlCommand(Sql, Connection))
            {
                Connection.Open();
                try
                {
                    command.ExecuteNonQuery();
                }
                finally
                {
                    Connection.Close();
                }
            }
        }

    }
}